<?php
	
class m_new_enrollee Extends MY_Model
{
	private $last_insert_id;
	private $CI;
	private $__table = 'enrollments';
	private $__seftable = 'student_enrollment_fees';
	
	public function __construct()
	{
		parent::__construct();
		$this->CI =& get_instance();
		$this->CI->load->model('m_level_fees','mlfees');
	}
	
	
	/*
		Insert new enrollee to enrollments table
		with verify time and code for verification
	*/
	public function enroll_new_student($data)
	{
		$sy = $this->c_sy;
		unset($data['password']);
		$data['e_sy_from'] = $sy->sy_from;
		$data['e_sy_to'] = $sy->sy_to;
		$data['e_ay_id'] = $sy->id;
		$data['e_created_at'] = NOW;
		$this->db->insert($this->__table,$data);
		$this->last_insert_id = $this->db->insert_id();
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;	
	}

	public function update_enrolle($data,$e_id)
	{
		$data['e_updated_at'] = NOW;
		$this->db->where('e_id', $e_id);
		$this->db->update('enrollments', $data);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;	
	}

	/*******************
	get applicant
	********************/
	public function get_applicant($e_id)
	{
		$sql = "SELECT e.e_grade_level,
									 e.guidance_recommend_level_id,
									 e.e_ay_id,
									 e.e_profile_id,
									 p.firstname,
									 p.middlename,
									 p.lastname,
									 p.name_ext,
									 p.dob,
									 p.contact_no,
									 p.previous_school
									 FROM enrollments e
									 LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
									 LEFT JOIN levels l ON e.e_grade_level = l.level_id
									 WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	/*
		Get all other fees assigned for a grade level then insert to student enrollment fees
		this is important so that if a fee is changed, the student will not be affected
		because they have their own record of the previous fees assigned to them
	*/
	public function get_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		//get all selected fees assigned for grade level
		//$data = $this->CI->mlfees->get_sel_fees($level_id);
		
		//if(!empty($data))
		//{
			//$count = 0;
			//foreach($data as $fees)
			//{
				//$s_e_fees[$count]['sef_enrollment_id'] = $enroll_id;
				//$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				//$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				//$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				//$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				//$s_e_fees[$count]['sef_created'] = NOW;
				//$s_e_fees[$count]['is_tuition_fee'] = $fees->is_tuition_fee;
				// $s_e_fees[$count]['is_cca_fee'] = $fees->is_cca_fee;
				// $s_e_fees[$count]['is_misc_fee'] = $fees->is_misc_fee;
				// $count++;
			//}
			// $this->db->insert_batch($this->__seftable,$s_e_fees);
			// if($this->db->affected_rows() >= 1)
			// {
				//set tuition fee and misc fee for the current level
				if($this->set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat) == TRUE)
				{
					$this->_check_enrollee_nationality($level_id,$enroll_id);
					return TRUE;
				}else{
					log_message('error','unable to set fees for current level id '.$enroll_id);
					return FALSE;
				}
			// }
		// }
		//else
		//{
			//return TRUE;
		//}
	}
	
	private function _check_enrollee_nationality($lid,$eid)
	{
		// get enrolee nationality
		$q = $this->db->select('nationality')
				 ->join('enrollments e','e.e_profile_id = p.profile_id')
				 ->where('e_id',$eid)
				 ->limit(1)
				 ->get('profiles p');
		
		
		if($q->num_rows() >=1 )
		{
			$nat= $q->row()->nationality;		
				
			//if nationality is filipino no foreign fee applied
			if(strtolower($nat) == 'filipino')
			{
				return TRUE;
			}else{
				//get and insert foreign fees
				$ff = 'INSERT INTO student_enrollment_fees (sef_enrollment_id,
															sef_fee_name,
															sef_fee_rate,
															sef_gperiod_id,
															sef_schoolyear_id,
															is_other_fee
															)
						SELECT ?,ff.fee_name,ff.fee_rate,?,?,1
						FROM foreign_fee ff
						WHERE ff.level_id = ?';
				
				$this->db->query($ff,array($eid,$this->c_gp->gp_id,$this->c_sy->id,$lid));
				
				return TRUE;
			}
		}else{
			return TRUE;
		}
	}
	
	/*
		function that inserts tuition to enrollee
	*/
	public function set_tuition_fees_set_for_current_level($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		/*
			Selects all tuition fee that is active.
		*/
		
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_tuition_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format tuition fees for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_tuition_fee'] = 1;
				$count++;
			}
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted tuition fee
				call method insert_miscellaneous_fee()
				this method will insert misc fee for the student
			*/
			if($this->db->affected_rows() >= 1 )
			{
				//call method to insert misc fee
				if($this->_insert_miscellaneous_fee($enroll_id,$sy,$level_id,$e_stat) == TRUE)
				{
					return TRUE;
				}else{
					log_message('error','Unable to insert Miscellaneous fees. ID'.$enroll_id);
					return TRUE;
				}
			}else{
				log_message('error','Unable to insert Tuition fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Tuition fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	/*
		Inserts misc fees to students, called after inserting the tuition fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	private function _insert_miscellaneous_fee($enroll_id,$sy,$level_id,$e_stat)
	{
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_misc_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
				
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_misc_fee'] = 1;
				$count++;
			}
			
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			
			if($this->db->affected_rows() >= 1)
			{
				//call method to insert misc fee
				if($this->_insert_matriculation_fee($enroll_id,$sy,$level_id,$e_stat) == TRUE)
				{
					return TRUE;
				}else{
					log_message('error','Unable to insert Matriculation fees. ID'.$enroll_id);
					return TRUE;
				}
			}else{
				log_message('error','Unable to insert Miscellaneous fees. ID'.$enroll_id);
				return TRUE;
			}

		}else{
			log_message('error','No data Miscellaneous fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	/*
		Inserts matriculation fees to students, called after inserting the miscellaneous fee,
		at method set_tuition_fees_set_for_current_level();
	*/
	private function _insert_matriculation_fee($id,$sy,$level_id,$e_stat)
	{
		$sql = "SELECT f.fee_name,f.fee_rate
				FROM fees f 
				WHERE f.is_cca_fee = 1
				AND f.grade_level = ?
				AND f.fee_stat = 'active'
				";
		if($e_stat=='old')
		{
			$sql .= " AND f.fee_old = 'yes'";
		}else{
			$sql .= " AND f.fee_new = 'yes'";
		}	
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees){
				$s_e_fees[$count]['sef_enrollment_id'] =$id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_cca_fee'] = 1;
				$count++;
			}
			
			//insert tuition fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			
			if($this->db->affected_rows() >= 1)
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Matriculation fees. ID'.$enroll_id);
				return TRUE;
			}

		}else{
			log_message('error','No data Matriculation fees yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	public function get_fees_set_for_foreign($level_id,$enroll_id,$gperiod,$sy,$e_stat)
	{
		/*
			Select foreign fee.
		*/
		
		$sql = "SELECT fee_name,fee_rate
				FROM foreign_fee 
				WHERE level_id = ?
				";
		$query = $this->db->query($sql,array($level_id,));
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format foreign fee for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_other_fee'] = 1;
				$count++;
			}
			//insert foreign fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted foreign fee
				return true else false
			*/
			if($this->db->affected_rows() >= 1 )
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Foreign fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Foreign fee yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	public function get_set_foreign_fee($enroll_id,$gperiod,$sy)
	{
		/*
			Select foreign fee.
		*/
		
		$sql = "SELECT fee_name,fee_rate
				FROM foreign_fee
				";
		$query = $this->db->query($sql);
		$data = $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		//format foreign fee for entry
		if(!empty($data))
		{
			$count = 0;
			foreach($data as $fees)
			{
				$s_e_fees[$count]['sef_enrollment_id'] =$enroll_id;
				$s_e_fees[$count]['sef_fee_name'] = $fees->fee_name;
				$s_e_fees[$count]['sef_fee_rate'] = $fees->fee_rate;
				$s_e_fees[$count]['sef_gperiod_id'] = $gperiod;
				$s_e_fees[$count]['sef_schoolyear_id'] = $sy;
				$s_e_fees[$count]['sef_created'] = NOW;
				$s_e_fees[$count]['is_other_fee'] = 1;
				$count++;
			}
			//insert foreign fees
			$this->db->insert_batch($this->__seftable,$s_e_fees);
			/*
				if successfully inserted foreign fee
				return true else false
			*/
			if($this->db->affected_rows() >= 1 )
			{
				return TRUE;
			}else{
				log_message('error','Unable to insert Foreign fees. ID'.$enroll_id);
				return TRUE;
			}
		}else{
			log_message('error','No data for Foreign fee yet. ID'.$enroll_id);
			return TRUE;
		}
	}
	
	public function add_further_profile_value($data,$id,$type='')
	{
		if($type == 'dev')
		{
			$input['dhc_pregnancy_length'] = $data['length_of_preg'];
			$input['dhc_delivery_form'] = $data['form_of_del'];
			$input['dhc_compications'] = $data['complications'];
			$input['dhc_sleep_time'] = $data['go_to_bed'];
			$input['dhc_wakup_time'] = $data['get_up_bed'];
			// $data['dhc_sleeping_disturbance'] = $data['sleeping_dist'];
			$input['dhc_sleeping_disturbance_what'] = $data['sleeping_dist'];
			$input['dhc_child_own_room'] = $data['own_room'];
			$input['dhc_room_shares_who'] = $data['room_shares'];
			$input['dhc_child_wet_bed'] = $data['wet_bed'];
			// $data['dhc_defect_growth'] = $data['observable_difficulties'];
			$input['dhc_defect_specify'] = $data['observable_difficulties'];
			$input['dhc_defect_when'] = $data['observable_difficulties_since_when'];
			$input['dhc_defect_actions'] = $data['observable_difficulties_action_taken'];
			// $data['dhc_speech_problems'] = $data['speech_problems'];
			$input['dhc_speech_problem_what'] = $data['speech_problems'];
			$input['dhc_speech_problem_when'] = $data['speech_problems_since_when'];
			$input['dhc_speech_problem_actions'] = $data['speech_problems_actions_taken'];
			$input['dhc_hearing_last_check'] = $data['hearing_last_checked'];
			// $data['dhc_hearing_problems'] = $data['hearing_problems'];
			$input['dhc_hearing_problems_what'] = $data['hearing_problems'];
			$input['dhc_hearing_problems_when'] = $data['hearing_problems_since_when'];
			$input['dhc_hearing_problem_actions'] = $data['hearing_problems_actions_taken'];
			$input['dhc_vision_checked'] = $data['vision_last_checked'];
			// $data['dhc_sight_problem'] = $data['vision_specify'];
			$input['dhc_sight_problems_what'] = $data['vision_specify'];
			$input['dhc_hinder_learning_process'] = $data['hinder_childs_learning'];
			// $data['ce_trauma_exp'] = $data[''];
			$input['ce_trauma_exp_what'] = $data['traumatic_experiences'];
			$input['ce_trauma_exp_age'] = $data['age_trauma'];
			$input['ce_trauma_exp_reaction'] = $data['trauma_reaction'];
			$input['ce_trauma_exp_affected'] = $data['how_trauma_affected_child'];
			// $data['ce_special_fears'] = $data['special_fears'];
			$input['ce_special_fears_what'] = $data['special_fears'];
			$input['ce_special_fears_when'] = $data['when_what_triggered_fear'];
			$input['ce_special_fears_handle'] = $data['how_do_you_handle'];
		}
			elseif($type == 'hlth')
		{	
			$input['hhc_allergies_reaction'] = $data['allergies_reaction'];
			$input['hhc_alergic_med_reaction'] = $data['drug_allergies_reaction'];
			$input['dhc_sight_problems_what'] = $data['eye_problem_spec'];
			$input['dhc_hearing_problems_what'] = $data['ear_problem_spec'];
			$input['hhc_diagnosed_specify'] = $data['major_ailment'];
			$input['hhc_recently_hospitalized'] = $data['recently_hospitalized'];
			$input['hhc_major_injury_specify'] = $data['major_injury'];
			$input['hhc_medication_school_hours'] = $data['medication_child_taking_spec'];
			$input['hhc_asthma_inhaler'] = isset($data['asthma_inhaler']) ? $data['asthma_inhaler'] : 'no';
			$input['hhc_asthma'] = isset($data['asthma']) ? $data['asthma'] : 'no';
			
		}elseif($type == 'pli'){
		
			$input['fathername'] = $data['father_name'];
			$input['father_mname'] = $data['father_mname'];
			$input['father_lname'] = $data['father_lname'];
			$input['father_nationality'] = $data['father_citizenship'];
			$input['father_visa'] = $data['father_visa'];
			$input['father_occupation'] =$data['father_occup'];
			$input['father_office_num'] =$data['father_office_tel'];
			$input['father_contact_no'] =$data['father_contact_no'];
			$input['father_contact_no_text'] = !isset($data['father_contact_no_text']) ? 'no' : 'yes';
			$input['mothername'] =$data['mother_name'];
			$input['mother_mname'] =$data['mother_mname'];
			$input['mother_lname'] =$data['mother_lname'];
			$input['mother_nationality'] =$data['mother_citizenship'];
			$input['mother_visa'] =$data['mother_visa'];
			$input['mother_occupation'] =$data['mother_occup'];
			$input['mother_office_num'] =$data['mother_office_tel'];
			$input['mother_contact_no'] =$data['mother_contact_no'];
			$input['mother_contact_no_text'] = !isset($data['mother_contact_no_text']) ? 'no' : 'yes';
			$input['step_father_name'] =$data['step_father_name'];
			$input['step_father_mname'] = $data['step_father_mname'];
			$input['step_father_lname'] = $data['step_father_lname'];
			$input['step_father_citizenship'] =$data['step_father_citizenship'];
			$input['step_father_visa'] =$data['step_father_visa'];
			$input['step_father_occup'] =$data['step_father_occup'];
			$input['step_father_office_tel'] =$data['step_father_office_tel'];
			$input['step_father_contact_no'] =$data['step_father_contact_no'];
			$input['step_father_contact_no_text'] = !isset($data['step_father_contact_no_text']) ? 'no' : 'yes';
			$input['step_mother_name'] =$data['step_mother_name'];
			$input['step_mother_mname'] = $data['step_mother_mname'];
			$input['step_mother_lname'] = $data['step_mother_lname'];
			$input['step_mother_citizenship'] =$data['step_mother_citizenship'];
			$input['step_mother_visa'] =$data['step_mother_visa'];
			$input['step_mother_occup'] =$data['step_mother_occup'];
			$input['step_mother_office_tel'] =$data['step_mother_office_tel'];
			$input['step_mother_contact_no'] =$data['step_mother_contact_no'];
			$input['step_mother_contact_no_text'] = !isset($data['step_mother_contact_no_text']) ? 'no' : 'yes';
			/*
			$input['guardian_name'] =$data['guardian_name'];
			$input['guardian_mname'] =$data['guardian_mname'];
			$input['guardian_lname'] =$data['guardian_lname'];
			$input['guardian_citizenship'] =$data['guardian_citizenship'];
			$input['guardian_visa'] =$data['guardian_visa'];
			$input['guardian_occup'] =$data['guardian_occup'];
			$input['guardian_office_tel'] =$data['guardian_office_tel'];
			$input['guardian_contact_no'] =$data['guardian_contact_no'];
			$input['guardian_contact_no_text'] = !isset($data['guardian_contact_no_text']) ? 'no' : 'yes';
			$input['emergency_name'] =$data['emergency_name'];
			$input['emergency_address'] =$data['emergency_address'];
			$input['emergency_office_tel'] =$data['emergency_office_tel'];
			$input['emergency_home_tel'] =$data['emergency_home_tel'];
			$input['emergency_home_no'] =$data['emergency_home_no'];
			*/
			$input['failure_to_provide'] = !isset($data['failure_to_provide']) ? 'no' : 'yes';
			$input['to_the_best'] = !isset($data['to_the_best']) ? 'no' : 'yes';
		
		}elseif($type == 'auth'){
		
			$input['auth_firstaid'] = $data['authfirst_aid'];
			$input['auth_emergency_care'] = $data['auth_emergency'];
			$input['auth_emergency_care_hospital'] = $data['auth_emergency_nearest_hosp'];
			$input['auth_oral_non_presc'] = $data['auth_oral_non_presc'];
			$input['first_permission'] = isset($data['first_permission']) ? 'yes' : 'no';
			$input['second_permission'] = isset($data['second_permission']) ? 'yes' : 'no';
			$input['third_permission'] = isset($data['third_permission']) ? 'yes' : 'no';
		}
		

		
		$this->db->set($input)->where('profile_id',$id)->update('profiles');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function count_id($data,$grade_level)
	{
		$input['id_cat'] = $data->cat;
		$input['id_year'] = $data->year;
		$input['id_num']= $data->num;
		$input['id_complete'] = $data->full;
		$input['grade_level'] = $grade_level;
		$query = $this->db->insert('student_id_counter',$input);
		
		if($this->db->affected_rows() >=1)
		{
			return TRUE;
		}else{
			log_message('error','ID '.$data->full.' Was not inserted to database ID counter');
		}
	}
	
	public function get_last_input_id()
	{
		return $this->last_insert_id;
	}
}